package com.onlineMIS.ORM.DAO.headQ.inventory;

import java.text.ParseException;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.HashSet;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Set;

import javax.servlet.FilterRegistration.Dynamic;

import org.apache.commons.lang.StringUtils;
import org.hibernate.criterion.DetachedCriteria;
import org.hibernate.criterion.Order;
import org.hibernate.criterion.Projections;
import org.hibernate.criterion.Restrictions;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.support.StaticApplicationContext;
import org.springframework.stereotype.Repository;

import com.onlineMIS.ORM.DAO.BaseDAO;
import com.onlineMIS.ORM.DAO.chainS.user.ChainStoreDaoImpl;
import com.onlineMIS.ORM.DAO.headQ.SQLServer.ClientDAOImpl;
import com.onlineMIS.ORM.DAO.headQ.SQLServer.SaleHistoryMSDAOImpl;
import com.onlineMIS.ORM.DAO.headQ.barCodeGentor.HeadQEntityTagDaoImpl;
import com.onlineMIS.ORM.DAO.headQ.barCodeGentor.ProductBarcodeDaoImpl;
import com.onlineMIS.ORM.DAO.headQ.custMgmt.HeadQCustDaoImpl;
import com.onlineMIS.ORM.entity.chainS.inventoryFlow.ChainInOutStock;
import com.onlineMIS.ORM.entity.chainS.user.ChainStore;
import com.onlineMIS.ORM.entity.headQ.SQLServer.ClientsMS;
import com.onlineMIS.ORM.entity.headQ.SQLServer.SaleHistory;
import com.onlineMIS.ORM.entity.headQ.barcodeGentor.HeadQTag;
import com.onlineMIS.ORM.entity.headQ.barcodeGentor.ProductBarcode;
import com.onlineMIS.ORM.entity.headQ.custMgmt.HeadQCust;
import com.onlineMIS.ORM.entity.headQ.inventory.SalesStatisticsDTO;
import com.onlineMIS.ORM.entity.headQ.report.HeadQDealingsReportVO;
import com.onlineMIS.ORM.entity.headQ.report.HeadQSalesStatisticReportItemVO;
import com.onlineMIS.ORM.entity.headQ.inventory.InventoryOrder;
import com.onlineMIS.ORM.entity.headQ.inventory.InventoryOrderDTO;
import com.onlineMIS.ORM.entity.headQ.inventory.InventoryOrderProduct;
import com.onlineMIS.action.headQ.ipad.IpadActionFormBean;
import com.onlineMIS.action.headQ.report.HeadQReportFormBean;
import com.onlineMIS.common.Common_util;
import com.onlineMIS.common.DateUtils;
import com.onlineMIS.common.loggerLocal;
import com.onlineMIS.filter.SystemParm;

@Repository
public class InventoryOrderDAOImpl extends BaseDAO<InventoryOrder> {
   
    @Autowired
    private SaleHistoryMSDAOImpl saleHistoryMSDAOImpl;

	@Autowired
	private HeadQCustDaoImpl custDaoImpl;
	@Autowired
	private ChainStoreDaoImpl chainStoreDaoImpl;
	@Autowired
	private ProductBarcodeDaoImpl productBarcodeDaoImpl;
	@Autowired
	private HeadQEntityTagDaoImpl headQEntityTagDaoImpl;
    
	@SuppressWarnings("unchecked")
	public List<InventoryOrder> search(DetachedCriteria criteria) {
		List<InventoryOrder> orderList= new ArrayList<InventoryOrder>();
		
	    orderList = getByCritera(criteria,false);

		return orderList;
	}

	public InventoryOrder retrieveOrder(int order_ID) {
        InventoryOrder order =null;

        order = get(order_ID, false);
        
        if (order != null)
            initialize(order);

		return order;
	}
	
	/**
	 * to calculate the statics of the quantity of the products bought before for the customer
	 * @param barcode
	 * @param customerid
	 * @return
	 */
	public int getQuantityBefore(final int pbId,final int clientId){
		List<Integer> statusList = new ArrayList<Integer>();
		statusList.add(InventoryOrder.STATUS_ACCOUNT_COMPLETE);
		int orderHis = this.getInventoryProductQuantity(clientId,pbId,statusList, null);
		
		return orderHis;
	}
	
	/**
	 * 获取公司已经发下去订单，但是客户还未确认
	 * @param barcode
	 * @param customerid
	 * @return
	 */
	public int getQuantityOnWay(final int pbId,final int clientId){
		String sql = "select sum( s2.quantity) from InventoryOrderProduct s2 where  s2.productBarcode.id = " +pbId+" and s2.order.order_type="+ InventoryOrder.TYPE_SALES_ORDER_W+" and s2.order.client_id = "+clientId+"  and s2.order.order_Status = " + InventoryOrder.STATUS_ACCOUNT_COMPLETE +"  and s2.order.chainConfirmStatus in ( " + InventoryOrder.STATUS_CHAIN_NOT_CONFIRM + ","+InventoryOrder.STATUS_CHAIN_PRODUCT_INCORRECT+")" ;
		int nums = 0;
		try {
		List<Object> values = this.executeHQLSelect(sql.toString(), null, null, false);
		if (values!= null && values.size() != 0) {
			for (Object record : values) {	
				if (record != null)
				   nums += (Long)record;
			}
		}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return nums;

	}
	
	/**
	 * to initialize the order
	 * @param order
	 */
	public void initialize(InventoryOrder order){
		this.getHibernateTemplate().initialize(order.getProduct_Set());
	}

	/**
	 * to copy the inventory order to another one
	 * @param orderInDB
	 * @return
	 */
	public InventoryOrder copy(InventoryOrder orderInDB) {
		evict(orderInDB);
		
		InventoryOrder order = orderInDB;
		
		order.setOrder_ID(0);
		
		Set<InventoryOrderProduct> orderProducts = order.getProduct_Set();
		for (InventoryOrderProduct orderProduct: orderProducts){
			orderProduct.setID(0);
		}
		
		return order;
	}
	
	public static String  getMaiduanS(InventoryOrder i) {
		if (i.getIsMaiduan() == 1)
			return "买断";
		else 
			return "";
	}
	
	public static String  getDaimaiS(InventoryOrder i) {
		if (i.getIsDaimai() == 1)
			return "代卖";
		else 
			return "";
	}
	public static String  getTransferS(InventoryOrder i) {
		if (i.getIsTransfer() == 1)
			return "调货";
		else 
			return "";
	}

	
	/**
	 * 根据orderID 查找客户clientId
	 * @param orderIDS
	 * @return
	 */
	public List<Integer> selectClientIDS(HeadQReportFormBean formBean,List<Integer> orderIDS) {
		
		String sql = "select DISTINCT( s1.client_id) from InventoryOrder s1 where s1.order_StartTime between ? and ? and order_Status = "+InventoryOrder.STATUS_ACCOUNT_COMPLETE+" and s1.order_ID IN(?) ";
		// 根据条件补充部分
		List<Object> list = new ArrayList<Object>();
		list.add(formBean.getStartDate());
		list.add(formBean.getEndDate());
		list.add(orderIDS);
		List<Integer> clientIds = new ArrayList<Integer>();
		if (orderIDS.size() !=0) {		
			List<Object> values = this.executeHQLSelect(sql.toString(), list.toArray(), null, true);
				if (values.size() != 0) {
					for (Object record : values) {
						Object[] records = (Object[]) record;
						clientIds.add(Common_util.getInt(records[0]));
					}
				}
		}else {
			return null;
		}				
		return clientIds;
	}

	
	/**
	 * 查询时间范围内的客户
	 * @param formBean
	 * @return
	 */
	public List<Integer> getClientIds(HeadQReportFormBean formBean) {
		//注意：销售单据使用：oeder_endTime 时间字段
		String sql = "select DISTINCT( s1.client_id) from InventoryOrder s1 where s1.order_EndTime between ? and ? and order_Status = " + InventoryOrder.STATUS_ACCOUNT_COMPLETE;
		// 根据条件补充部分
		List<Object> list = new ArrayList<Object>();
		list.add(Common_util.formStartDate(formBean.getStartDate()));
		list.add(Common_util.formEndDate(formBean.getEndDate()));

		List<Integer> clientIds = new ArrayList<Integer>();

		List<Object> values = this.executeHQLSelect(sql.toString(), list.toArray(), null, true);
			if (values.size() != 0) {
				for (Object record : values) {
					clientIds.add(Common_util.getInt(record));
				}
			}
		return clientIds;
	}

	/**
	 * 根据客户id查询客户退换货品销售金额和数量
	 * 
	 * @param formBean
	 * @param clientId
	 * @return
	 */
	public InventoryOrderDTO selectReturnOrder(HeadQReportFormBean formBean, int clientId,List<Integer> productIds,boolean isDaimai,boolean isMaiDuan) {
		String sql = "select SUM(s2.quantity),SUM(s2.quantity * s2.recCost),SUM(s2.quantity * s2.wholeSalePrice)  from InventoryOrder s1, InventoryOrderProduct s2 where s1.order_ID = s2.order.order_ID and s1.client_id = ? and s1.order_StartTime between ? and ? and  s1.order_Status = 3  and s1.order_type = ?";
		// 根据条件补充部分
		InventoryOrderDTO dto = new InventoryOrderDTO();
		List<Object> list = new ArrayList<Object>();
		list.add(clientId);
		list.add(Common_util.formStartDate(formBean.getStartDate()));
		list.add(Common_util.formEndDate(formBean.getEndDate()));
		list.add(InventoryOrder.TYPE_SALES_RETURN_ORDER_W);
		
		if (isDaimai) {
			sql += " and s1.isDaimai = 1";
		}
		
		if (isMaiDuan) {
			sql += " and s1.isMaiduan = 1";
		}
		
		if (productIds.size() != 0) {
			sql += " and  s2.productBarcode.id in (";
			for (int i = 0; i < productIds.size(); i++) {
				if (i == productIds.size() - 1) {
					sql +=productIds.get(i).toString() + ")";
				} else {
					sql += productIds.get(i).toString() + ",";
				}
			}

		}
			List<Object> values = this.executeHQLSelect(sql.toString(), list.toArray(), null, true);

			if (values.size() != 0) {
				for (Object record : values) {
					Object[] records = (Object[]) record;
					dto.setTotalQuantity(Common_util.getInt(records[0]));
					dto.setTotalRecCost(Common_util.getDouble(records[1]));
					dto.setTotalWholePrice(Common_util.getDouble(records[2]));
				}
			} else {
				dto.setTotalQuantity(0);
				dto.setTotalWholePrice(0.0);
				dto.setTotalRecCost(0.0);
			}
		return dto;
	}

	
	/**
	 * 根据客户ids和productIds查询客户销售信息
	 * @param clientIds
	 * @param productIds
	 * @param formBean
	 * @return
	 */
	public List<InventoryOrderDTO> selectUnitSalesByClientId(List<Integer> clientIds, List<Integer> productIds,HeadQReportFormBean formBean) {

		List<InventoryOrderDTO> orderDTOs = new ArrayList<InventoryOrderDTO>();
		//注意：这里选取的时间为：order_EndTime 单据审核时间
		String sql = "select s1.order_ID, s1.client_id,s1.order_type,s1.isDaimai,s1.isMaiduan,SUM(s2.quantity),SUM(s2.quantity * s2.recCost),SUM(s2.quantity * s2.wholeSalePrice) from InventoryOrder s1 ,InventoryOrderProduct s2  where s1.order_ID = s2.order.order_ID and s1.order_EndTime between ? and ? and s1.order_Status = ?  ";

		List<Object> list = new ArrayList<Object>();
		list.add(Common_util.formStartDate(formBean.getStartDate()));
		list.add(Common_util.formEndDate(formBean.getEndDate()));
		list.add(InventoryOrder.STATUS_ACCOUNT_COMPLETE);

		//增加客户id的查询
		if (clientIds != null) {
			String clientIdString = "and s1.client_id in " + Common_util.parseList2SQLString(clientIds);
			sql += clientIdString;
		}
        
		// 货品product_ids 
		if ( productIds != null) {
				String productId = "and s2.productBarcode.id in " + Common_util.parseList2SQLString(productIds);
				sql += productId;
		}
		sql += " group by s1.order_ID, s1.client_id,s1.order_type,s1.isDaimai,s1.isMaiduan";
				
		List<Object> values = this.executeHQLSelect(sql.toString(), list.toArray(), null, true);

				if (values.size() != 0) {
					for (Object record : values) {
						InventoryOrderDTO dto = new InventoryOrderDTO();
						Object[] records = (Object[]) record;
						dto.setOrderId(Common_util.getInt(records[0]));
						dto.setClientId(Common_util.getInt(records[1]));
						dto.setOrderType(Common_util.getInt(records[2]));
						dto.setIsDaimai(Common_util.getInt(records[3]));
						dto.setIsMaiduan(Common_util.getInt(records[4]));
						dto.setTotalQuantity(Common_util.getInt(records[5]));
						dto.setTotalRecCost(Common_util.getDouble(records[6]));
						dto.setTotalWholePrice(Common_util.getDouble(records[7]));

						orderDTOs.add(dto);
					}
		}	

		return orderDTOs;
	}
	
	
	

	/**
	 * 根据客户ids和productIds查询客户买断或者代卖的销售信息
	 * @param clientIds
	 * @param productIds
	 * @param formBean
	 * @param isDaimai
	 * @param isMaiDuan
	 * @return
	 */
	public Map<Integer, InventoryOrderDTO> selectUnitSales(List<Integer> clientIds, List<Integer> productIds,HeadQReportFormBean formBean,boolean isDaimai,boolean isMaiDuan) {
		Map<Integer, InventoryOrderDTO> map = new HashMap<Integer, InventoryOrderDTO>();
			String sql = "select s1.client_id,s1.client_name, SUM(s2.quantity),SUM(s2.quantity * s2.recCost),SUM(s2.quantity * s2.wholeSalePrice) from InventoryOrder s1 ,InventoryOrderProduct s2  where s1.order_ID = s2.order.order_ID and s1.order_StartTime between ? and ? and s1.order_Status = ?  and s1.order_type = ? ";

			List<Object> list = new ArrayList<Object>();
			list.add(Common_util.formStartDate(formBean.getStartDate()));
			list.add(Common_util.formEndDate(formBean.getEndDate()));
			list.add(InventoryOrder.STATUS_ACCOUNT_COMPLETE);
			list.add(InventoryOrder.TYPE_SALES_ORDER_W);
			
			if (isDaimai) {
				sql += " and s1.isDaimai = 1";
			}
			
			if (isMaiDuan) {
				sql += " and s1.isMaiduan = 1";
			}

			// 货品product_ids ,客户client_ids
			if (clientIds.size() != 0 || productIds.size() != 0) {
				if (clientIds.size() != 0) {
					String clientId = "and s1.client_id in(";
					for (int i = 0; i < clientIds.size(); i++) {
						clientId += clientIds.get(i).toString() + ",";
						if (i == clientIds.size() - 1) {
							clientId += clientIds.get(i) + ")";
						}
					}
					sql += clientId;
				}

				if (productIds.size() != 0) {
					String productId = "and s2.productBarcode.id in(";
					for (int i = 0; i < productIds.size(); i++) {
						productId += productIds.get(i).toString() + ",";
						if (i == productIds.size() - 1) {
							productId += productIds.get(i) + ")";
						}
					}
					sql += productId;
				}
				sql += " group by s1.client_id";
					List<Object> values = this.executeHQLSelect(sql.toString(), list.toArray(), null, true);

					if (values.size() != 0) {
						for (Object record : values) {
							InventoryOrderDTO dto = new InventoryOrderDTO();
							Object[] records = (Object[]) record;
							dto.setClientId(Common_util.getInt(records[0]));
							dto.setClientName(records[1].toString());
							dto.setTotalQuantity(Common_util.getInt(records[2]));
							dto.setTotalRecCost(Common_util.getDouble(records[3]));
							dto.setTotalWholePrice(Common_util.getDouble(records[4]));

							map.put(Common_util.getInt(records[0]), dto);
						}
					}
			}		

		return map;
	}

	
	/**
	 * 根据客户id和货品条件，查询该客户单位销售统计的销售单据明细（批发销售单&&批发退货单）
	 * @param formBean
	 * @param clientId
	 * @param productIds
	 * @return
	 */
	public String selectOrderIds(HeadQReportFormBean formBean, Integer clientId, List<Integer> productIds) {
		String sql = "select DISTINCT(s1.order_ID) from InventoryOrder s1 ,InventoryOrderProduct s2  where s1.order_ID = s2.order.order_ID and s1.order_StartTime between ? and ? and s1.order_Status = ?  and (s1.order_type = ? or s1.order_type = ? )  and s1.client_id = ?";
		String orderIds = "";
		List<Object> list = new ArrayList<Object>();
		list.add(Common_util.formStartDate(formBean.getStartDate()));
		list.add(Common_util.formEndDate(formBean.getEndDate()));
		list.add(InventoryOrder.STATUS_ACCOUNT_COMPLETE);
		list.add(InventoryOrder.TYPE_SALES_ORDER_W);
		list.add(InventoryOrder.TYPE_SALES_RETURN_ORDER_W);
		list.add(clientId);

		if (productIds.size() != 0) {
			String productId = "and s2.productBarcode.id in(";
			for (int i = 0; i < productIds.size(); i++) {
				if (i == productIds.size() - 1) {
					productId += productIds.get(i) + ")";
				} else {
					productId += productIds.get(i).toString() + ",";
				}
			}
			sql += productId;
		}

		List<Object> values = this.executeHQLSelect(sql.toString(), list.toArray(), null, true);

		if (values.size() != 0) {
				for (Object record : values) {
					orderIds += record + ",";
				}
		}
		return orderIds;
	}


	
	/**
	 * 货品类别查询动销率
	 * @param yearId
	 * @param quarterId
	 * @param categoryLevel
	 * @param categoryId
	 * @param product
	 * @param chainId
	 * @return
	 */
	public Map<Integer,Integer> selectDynamicSales(Integer yearId, Integer quarterId, Integer categoryLevel,Integer categoryId,Integer product,Integer chainId) {		
			Map<Integer, Integer> map = new HashMap<Integer, Integer>();						
			String sql = "";
			boolean isSelect = false;			
			if (categoryLevel == null) {
				sql = "select a.categoryLevel1.category_ID,";
				isSelect = true;
			}			
			if (categoryId == null && isSelect != true) {
				sql = "select a.category.category_ID,";
				isSelect = true;
			}
			if (product == null && isSelect != true) {
				sql = "select d.productBarcode.id,";
			}
			
			sql += "SUM(d.quantity) from Product a, ProductBarcode b, InventoryOrder c, InventoryOrderProduct d where	a.productId = b.product.productId and b.id = d.productBarcode.id and c.order_ID = d.order.order_ID and c.order_Status = 3 ";
			List<Object> list = new ArrayList<Object>();
			if(yearId != null) {
				sql += " and a.year.year_ID = ? ";
				list.add(yearId);
			}
			if(quarterId != null) {
				sql += " and a.quarter.quarter_ID = ? ";
				list.add(quarterId);
			}		
			if(categoryLevel != null) {
				sql += " and a.categoryLevel1.category_ID = ? ";
				list.add(categoryLevel);
			}
			if (categoryId !=null) {
				sql += " and a.category.category_ID = ? ";
				list.add(categoryId);
			}
			
			if(chainId == ChainStore.IS_SELECT_ALL) {
				sql += "AND c.order_type = 0 AND c.client_id IN " + chainStoreDaoImpl.getAllClientIdsAsString(); //获取所有运营店铺的客户id client_id
			}else {
				sql += "AND c.order_type = 0  AND c.client_id = ? ";
				ChainStore store = chainStoreDaoImpl.get(chainId, true);
				if (store != null) {
					HeadQCust cust = custDaoImpl.get(store.getClient_id(), true);		
					if (cust != null) {
						list.add(cust.getId());
					}
				}
			}						
			sql += " and a.category.category_ID NOT IN(9,799) and c.order_EndTime between ? and ?  ";
			boolean isGroup = false;
			
			if (categoryLevel == null) {
				sql += "group by a.categoryLevel1.category_ID";
				isGroup = true;
			}
			if (categoryId == null && isGroup != true) {
				sql += "group by a.category.category_ID";
				isGroup = true;
			}
			if (product == null && isGroup != true) {
				sql += "group by d.productBarcode.id";
			}			
			list.add(Common_util.formStartDate(DateUtils.getBeforeYearHalf()));
			list.add(Common_util.formEndDate(DateUtils.getNowDate()));
			List<Object> values = this.executeHQLSelect(sql, list.toArray(), null, true);
			
			if (values.size() !=0) {				
				for (Object record : values) {
					Object[] records = (Object[]) record;
					String productInfo = Common_util.getString(records[0]);
					int totalQuantity = Common_util.getInt(records[1]);
					map.put(Integer.valueOf(productInfo),totalQuantity);
				}
			}
			return map;								
		}

	/**
	 * 品牌查询动销率
	 * @param yearId
	 * @param quarterId
	 * @param brandId
	 * @param product
	 * @param chainId
	 * @return
	 */
	public Map<Integer, Integer> selectDynamicSalesByBrand(Integer yearId, Integer quarterId, Integer brandId,Integer product,Integer chainId) {
		Map<Integer, Integer> map = new HashMap<Integer, Integer>();						
		String sql = "";
		boolean isSelect = false;		
		if (brandId == null && isSelect != true) {
			sql = "select a.brand.brand_ID,";
			isSelect = true;
		}
		if (product == null && isSelect != true) {
			sql = "select d.productBarcode.id,";
		}
		sql += "SUM(d.quantity) from Product a, ProductBarcode b, InventoryOrder c, InventoryOrderProduct d where	a.productId = b.product.productId and b.id = d.productBarcode.id and c.order_ID = d.order.order_ID and c.order_Status = 3 ";
		List<Object> list = new ArrayList<Object>();
		if(yearId != null) {
			sql += " and a.year.year_ID = ? ";
			list.add(yearId);
		}
		if(quarterId != null) {
			sql += " and a.quarter.quarter_ID = ? ";
			list.add(quarterId);
		}		
		if(brandId != null) {
			sql += " and a.brand.brand_ID = ? ";
			list.add(brandId);
		}
				
		if(chainId == ChainStore.IS_SELECT_ALL) {
			sql += "AND c.order_type = 0 AND c.client_id IN " + chainStoreDaoImpl.getAllClientIdsAsString(); //获取所有运营店铺的客户id client_id
		}else {
			sql += "AND c.order_type = 0  AND c.client_id = ? ";
			ChainStore store = chainStoreDaoImpl.get(chainId, true);
			if (store != null) {
				HeadQCust cust = custDaoImpl.get(store.getClient_id(), true);		
				if (cust != null) {
					list.add(cust.getId());
				}
			}
		}		
		sql += " and a.category.category_ID NOT IN(9,799) and c.order_EndTime between ? and ?  ";
		boolean isGroup = false;		
		
		if (brandId == null && isGroup != true) {
			sql += "group by a.brand.brand_ID";
			isGroup = true;
		}
		if (product == null && isGroup != true) {
			sql += "group by d.productBarcode.id";
		}
		
		list.add(Common_util.formStartDate(DateUtils.getBeforeYearHalf()));
		list.add(Common_util.formEndDate(DateUtils.getNowDate()));
		List<Object> values = this.executeHQLSelect(sql, list.toArray(), null, true);
		
		if (values.size() !=0) {				
			for (Object record : values) {
				Object[] records = (Object[]) record;
				int productInfo = Common_util.getInt(records[0]);
				int totalQuantity = Common_util.getInt(records[1]);
				map.put(productInfo,totalQuantity);
			}
		}
		return map;	
	}
	
	
	
	/**
	 * 分页查询销售信息
	 * 
	 * @param formBean
	 */
	public int selectSalesTotal(HeadQReportFormBean formBean) {
		List<Object> list = new ArrayList<Object>();
		int total = 0;
		String sql = "select d.productBarcode.id from Product a, ProductBarcode b, InventoryOrder c, InventoryOrderProduct d where	a.productId = b.product.productId and b.id = d.productBarcode.id and c.order_ID = d.order.order_ID and c.order_Status = 3 and c.order_type = 0 "; 		
		if (formBean.getYear().getYear_ID() != 0) {
			sql += " AND a.year.year_ID = ?";
			list.add(formBean.getYear().getYear_ID());
		}
		if (formBean.getQuarter().getQuarter_ID() != 0) {
			sql += " AND a.quarterquarter_ID = ?";
			list.add(formBean.getQuarter().getQuarter_ID());
		}
		if (formBean.getBrand().getBrand_ID() != 0) {
			sql += " AND a.brand.brand_ID = ?";
			list.add(formBean.getBrand().getBrand_ID());
		}
		if (formBean.getCust().getId() != 0) {
			sql += " AND c.client_id = ?";
			list.add(formBean.getCust().getId());
		}
		if (formBean.getCategoryLevel().getCategory_ID() !=0) {
			sql += " AND a.categoryLevel1.category_ID = ?";
			list.add(formBean.getCategoryLevel().getCategory_ID());
		}
		if (formBean.getCategory().getCategory_ID() !=0) {
			sql += " AND a.category.category_ID = ?";
			list.add(formBean.getCategory().getCategory_ID());
		}
		if (StringUtils.isNotEmpty(formBean.getBarcode())) {
			ProductBarcode barcode = productBarcodeDaoImpl.getByBarcode(formBean.getBarcode());
			if (barcode != null) {
				sql += " AND d.productBarcode.id = ?";
				list.add(barcode.getId());
			}
		}
		
		sql += "AND c.order_EndTime between ? and ?  group by d.productBarcode.id";
		list.add(Common_util.formStartDate(formBean.getStartDate()));
		list.add(Common_util.formEndDate(formBean.getEndDate()));

		List<Object> values = this.executeHQLSelect(sql, list.toArray(), null, true);

		if (values.size() != 0) {
			total = values.size();
		}
		return total;
	}
	

	
	/**
	 * 分页查询销售信息
	 * @param formBean
	 */
	public List<SalesStatisticsDTO> selectSales(HeadQReportFormBean formBean,Integer page, Integer rowPerPage, String sort, String sortOrder) {
		List<Object> list = new ArrayList<Object>();
		List<SalesStatisticsDTO> dtos = new ArrayList<SalesStatisticsDTO>();
		String sql = "select d.productBarcode.id,d.productBarcode.barcode,a.productCode,b.color.colorId, SUM(d.quantity),SUM(d.recCost * d.quantity), SUM(d.wholeSalePrice * d.quantity) from Product a, ProductBarcode b, InventoryOrder c, InventoryOrderProduct d where	a.productId = b.product.productId and b.id = d.productBarcode.id and c.order_ID = d.order.order_ID and  c.order_Status = 3 and c.order_type = 0 "; 		
		if (formBean.getYear().getYear_ID() != 0) {
			sql += " AND a.year.year_ID = ?";
			list.add(formBean.getYear().getYear_ID());
		}
		if (formBean.getQuarter().getQuarter_ID() != 0) {
			sql += " AND a.quarterquarter_ID = ?";
			list.add(formBean.getQuarter().getQuarter_ID());
		}
		if (formBean.getBrand().getBrand_ID() != 0) {
			sql += " AND a.brand.brand_ID = ?";
			list.add(formBean.getBrand().getBrand_ID());
		}
		if (formBean.getCust().getId() != 0) {
			sql += " AND c.client_id = ?";
			list.add(formBean.getCust().getId());
		}
		if (formBean.getCategoryLevel().getCategory_ID() !=0) {
			sql += " AND a.categoryLevel1.category_ID = ?";
			list.add(formBean.getCategoryLevel().getCategory_ID());
		}
		if (formBean.getCategory().getCategory_ID() !=0) {
			sql += " AND a.category.category_ID = ?";
			list.add(formBean.getCategory().getCategory_ID());
		}
		if (StringUtils.isNotEmpty(formBean.getBarcode())) {
			ProductBarcode barcode = productBarcodeDaoImpl.getByBarcode(formBean.getBarcode());
			if (barcode != null) {
				sql += " AND d.productBarcode.id = ?";
				list.add(barcode.getId());
			}
		}
		
		sql += "AND c.order_EndTime between ? and ?  group by d.productBarcode.id";
			
		list.add(Common_util.formStartDate(formBean.getStartDate()));
		list.add(Common_util.formEndDate(formBean.getEndDate()));
		List<Integer> pageInfo = new ArrayList<Integer>();
		pageInfo.add(Common_util.getFirstRecord(page, rowPerPage));
		pageInfo.add(rowPerPage);
		Integer[] pages = pageInfo.toArray(new Integer[]{});
		List<Object> values = this.executeHQLSelect(sql, list.toArray(), pages, true);		

		if (values.size() !=0) {
			for (Object record : values) {				
				Object[] records = (Object[]) record;
				int pbId = Common_util.getInt(records[0]);
				String barcode = Common_util.getString(records[1]);
				String productCode = Common_util.getString(records[2]);
				int colorId = Common_util.getInt(records[3]);
				int total = Common_util.getInt(records[4]);
				double cost = Common_util.getDouble(records[5]);
				double sales = Common_util.getDouble(records[6]);
				SalesStatisticsDTO dto = new SalesStatisticsDTO(pbId,barcode,productCode,colorId,total,cost,sales);
				dtos.add(dto);
			}
		}	
		return dtos;
	}

	
	/**
	 * 分页查询销售退货信息
	 * @param formBean
	 */
	public Map<Integer, SalesStatisticsDTO> selectReturnSales(Date startDate,Date endDate,List<Integer> ids) {
		List<Object> list = new ArrayList<Object>();
		Map<Integer, SalesStatisticsDTO> map = new HashMap<Integer, SalesStatisticsDTO>();
		String sql = "select d.productBarcode.id,SUM(d.quantity),SUM(d.recCost * d.quantity), SUM(d.wholeSalePrice * d.quantity)  from Product a, ProductBarcode b, InventoryOrder c, InventoryOrderProduct d where	a.productId = b.product.productId and b.id = d.productBarcode.id and c.order_ID = d.order.order_ID and c.order_Status = 3 and c.order_type = 1 "; 				
		
		if (ids.size() !=0) {
			sql += " AND d.productBarcode.id in (";
			for (int i = 0; i < ids.size(); i++) {
				if (i == ids.size() -1) {
					sql += ids.get(i) + ")";
				}else {
					sql += ids.get(i) + ",";
				}
			}
			sql += "AND c.order_EndTime between ? and ?  group by d.productBarcode.id";
			
			list.add(Common_util.formStartDate(startDate));
			list.add(Common_util.formEndDate(endDate));
			List<Object> values = this.executeHQLSelect(sql, list.toArray(), null, true);
			
			if (values.size() !=0) {
				for (Object record : values) {				
					Object[] records = (Object[]) record;
					int pbId = Common_util.getInt(records[0]);
					int total = Common_util.getInt(records[1]);
					double cost = Common_util.getDouble(records[2]);
					double sales = Common_util.getDouble(records[3]);
					SalesStatisticsDTO dto = new SalesStatisticsDTO(pbId,null,null,0,total,cost,sales);
					map.put(pbId, dto);
				}
			}
			
		}

		return map;
	}

	public HeadQSalesStatisticReportItemVO selectSalesFooters(HeadQReportFormBean formBean) {
		List<Object> list = new ArrayList<Object>();
		HeadQSalesStatisticReportItemVO vo = new HeadQSalesStatisticReportItemVO();
		String sql = "select c.order_type, SUM(d.quantity),SUM(d.recCost * d.quantity), SUM(d.wholeSalePrice * d.quantity) from Product a, ProductBarcode b, InventoryOrder c, InventoryOrderProduct d where	a.productId = b.product.productId and b.id = d.productBarcode.id and c.order_ID = d.order.order_ID and  c.order_Status = 3 "; 		
		if (formBean.getYear().getYear_ID() != 0) {
			sql += " AND a.year.year_ID = ?";
			list.add(formBean.getYear().getYear_ID());
		}
		if (formBean.getQuarter().getQuarter_ID() != 0) {
			sql += " AND a.quarterquarter_ID = ?";
			list.add(formBean.getQuarter().getQuarter_ID());
		}
		if (formBean.getBrand().getBrand_ID() != 0) {
			sql += " AND a.brand.brand_ID = ?";
			list.add(formBean.getBrand().getBrand_ID());
		}
		if (formBean.getCust().getId() != 0) {
			sql += " AND c.client_id = ?";
			list.add(formBean.getCust().getId());
		}
		if (formBean.getCategoryLevel().getCategory_ID() !=0) {
			sql += " AND a.categoryLevel1.category_ID = ?";
			list.add(formBean.getCategoryLevel().getCategory_ID());
		}
		if (formBean.getCategory().getCategory_ID() !=0) {
			sql += " AND a.category.category_ID = ?";
			list.add(formBean.getCategory().getCategory_ID());
		}
		if (StringUtils.isNotEmpty(formBean.getBarcode())) {
			ProductBarcode barcode = productBarcodeDaoImpl.getByBarcode(formBean.getBarcode());
			if (barcode != null) {
				sql += " AND d.productBarcode.id = ?";
				list.add(barcode.getId());
			}
		}
		
		sql += "AND c.order_EndTime between ? and ? group by c.order_type";
			
		list.add(Common_util.formStartDate(formBean.getStartDate()));
		list.add(Common_util.formEndDate(formBean.getEndDate()));
		List<Object> values = this.executeHQLSelect(sql, list.toArray(), null, true);	
		Map<Integer, SalesStatisticsDTO> map = new HashMap<Integer, SalesStatisticsDTO>();

		if (values.size() !=0) {
			for (Object record : values) {				
				Object[] records = (Object[]) record;
				int orderType = Common_util.getInt(records[0]);
				int total = Common_util.getInt(records[1]);
				double cost = Common_util.getDouble(records[2]);
				double sales = Common_util.getDouble(records[3]);
				SalesStatisticsDTO dto = new SalesStatisticsDTO();
				dto.setTotal(total);
				dto.setCost(cost);
				dto.setSales(sales);
				map.put(orderType, dto);
			}
		}	
		if (map.size() >1) {
			
			if (map.get(InventoryOrder.TYPE_SALES_ORDER_W) != null) {
				SalesStatisticsDTO dto = map.get(InventoryOrder.TYPE_SALES_ORDER_W);
				vo.setSalesQ(dto.getTotal());
				vo.setSalesCost(dto.getCost());
				vo.setSalesPrice(dto.getSales());				
			}
			if (map.get(InventoryOrder.TYPE_SALES_RETURN_ORDER_W) != null) {
				SalesStatisticsDTO dto = map.get(InventoryOrder.TYPE_SALES_RETURN_ORDER_W);
				vo.setReturnQ(dto.getTotal());
				vo.setReturnCost(dto.getCost());
				vo.setReturnPrice(dto.getSales());				
			}
		}
		
		return vo;
	}

		/**
	 * 查询时间范围内采购货品的客户集合
	 * @param startTime
	 * @param endTime
	 * @return
	 */
	@SuppressWarnings("unused")
	public List<Integer> selectCIdsByOrderTime(Date startTime, Date endTime) {
		String sql = "select DISTINCT( s1.client_id) from InventoryOrder s1 where s1.order_StartTime between ? and ?  ";
		// 根据条件补充部分
		List<Object> list = new ArrayList<Object>();
		list.add(Common_util.formStartDate(startTime));
		list.add(Common_util.formEndDate(endTime));
		List<Integer> clientIds = new ArrayList<Integer>();
		List<Object> values = this.executeHQLSelect(sql.toString(), list.toArray(), null, true);
		if (values.size() != 0) {
			for (Object record : values) {				
				clientIds.add(Common_util.getInt(record));
			}
		}
		return clientIds;
	}

	/**
	 * 获取Pda 草稿,pda完成状况的客户选货数量
	 * @param clientIdObj
	 * @param pbId
	 * @param endDate 某个日期之后的数量集合
	 * @return
	 */
		public int getInventoryProductQuantity(int client_id, int pbId, List<Integer> statusSet, String endDate) {
			
			String statusS = Common_util.parseList2SQLString(statusSet);
			
			StringBuffer whereClause = new StringBuffer();
			if (client_id != Common_util.ALL_RECORD)
				whereClause.append(" and s2.order.client_id = "+client_id);
			
			if (endDate != null)
				whereClause.append(" and s2.order.order_EndTime >= '" + endDate + "'");
			
			String sql = "select sum( s2.quantity) from InventoryOrderProduct s2 where  s2.productBarcode.id = " +pbId+" and s2.order.order_type="+ InventoryOrder.TYPE_SALES_ORDER_W+"  and s2.order.order_Status in " + statusS + whereClause;
			int nums = 0;
			try {
			List<Object> values = this.executeHQLSelect(sql.toString(), null, null, false);
			if (values!= null && values.size() != 0) {
				for (Object record : values) {	
					if (record != null)
					   nums += (Long)record;
				}
			}
			} catch (Exception e) {
				e.printStackTrace();
			}
			return nums;
		}

	/**
	 * 查询所有待配货/配货单据单据
	 * @return
	 * @throws ParseException 
	 */
	public List<InventoryOrder> searchPDAOrdersByStatus(Set<Integer> statusSet, int custRange, Date startDate, Date endDate)  {
		List<InventoryOrder> orderList= new ArrayList<InventoryOrder>();		
		DetachedCriteria criteria = DetachedCriteria.forClass(InventoryOrder.class);
		
		List<Integer> custsList = getClientIdByRange(custRange);

		
		criteria.add(Restrictions.in("client_id", custsList));//排除盘点机
		
		criteria.add(Restrictions.in("order_Status", statusSet));
		
		criteria.add(Restrictions.between("order_StartTime", Common_util.formStartDate(startDate), Common_util.formEndDate(endDate) ));
		//criteria.addOrder(Order.desc("client_id"));
		criteria.addOrder(Order.desc("order_StartTime"));
		orderList = getByCritera(criteria, true);
		return orderList;
	}
	
	public List<Integer> getClientIdByRange(int custRange) {
		List<Integer> custIds = new ArrayList<Integer>();
		
		switch (custRange) {
		case Common_util.ALL__RANGE:
			custIds.addAll(headQEntityTagDaoImpl.selectIdsByTagId(HeadQTag.HEADQ_QX_CUST_TAG_ID));
			custIds.addAll(headQEntityTagDaoImpl.selectIdsByTagId(HeadQTag.HEADQ_XLC_CUST_TAG_ID));
			break;
		case Common_util.QX_RANGE:
			custIds.addAll(headQEntityTagDaoImpl.selectIdsByTagId(HeadQTag.HEADQ_QX_CUST_TAG_ID));
			break;
		case Common_util.XLC_RANGE:
			custIds.addAll(headQEntityTagDaoImpl.selectIdsByTagId(HeadQTag.HEADQ_XLC_CUST_TAG_ID));
			break;				
		default:
			break;
		}
		
		return custIds;
	}

	/**
	 * 获取所有待配客户数量
	 * @return
	 * @throws ParseException 
	 */
	public int selectIpadNoEquidpped(List<Integer> custIds){
		
		String clientIdSql = Common_util.parseList2SQLString(custIds);
		
		String sql = "select count(DISTINCT s1.client_id) from InventoryOrder s1 where s1.order_StartTime between ? and ? and s1.client_id in "+clientIdSql+"  and ( s1.order_Status="+ InventoryOrder.STATUS_PDA_COMPLETE + "  or s1.order_Status="+ InventoryOrder.STATUS_PEIHUO_INPROCESS +")";
		// 根据条件补充部分
		List<Object> list = new ArrayList<Object>();
		list.add(Common_util.formStartDate(Common_util.getLastWeekDate()));
		list.add(Common_util.formEndDate(Common_util.getToday()));
		
		return this.executeHQLCount(sql.toString(), list.toArray(), true);
	}

	/**
	 * 获取所有  待配 单据数量
	 * @return
	 * @throws ParseException 
	 */
	public int selectIpadOrders(List<Integer> custIds) {
		String clientIdSql = Common_util.parseList2SQLString(custIds);
		
		
		String sql = "select count(s1.order_ID) from InventoryOrder s1 where s1.order_StartTime between ? and ?  and s1.client_id in "+clientIdSql+"   and ( s1.order_Status="+ InventoryOrder.STATUS_PDA_COMPLETE + "  or s1.order_Status="+ InventoryOrder.STATUS_PEIHUO_INPROCESS +")";
		// 根据条件补充部分
		List<Object> list = new ArrayList<Object>();
		list.add(Common_util.formStartDate(Common_util.getLastWeekDate()));
		list.add(Common_util.formEndDate(Common_util.getToday()));
		
		return this.executeHQLCount(sql.toString(), list.toArray(), true);
	}
	
	/**
	 * 获取所有已配客户数量
	 * @return
	 * @throws ParseException 
	 */
	public int selectIpadAlread(List<Integer> custIds) {
		String clientIdSql = Common_util.parseList2SQLString(custIds);
		
		String sql = "select count(distinct s1.client_id) from InventoryOrder s1 where s1.order_StartTime between ? and ? and  s1.order_Status="+ InventoryOrder.STATUS_PEIHUO_COMPLETE +"  and s1.client_id in "+clientIdSql;
		// 根据条件补充部分
		List<Object> list = new ArrayList<Object>();
		list.add(Common_util.formStartDate(Common_util.getToday()));
		list.add(Common_util.formEndDate(Common_util.getToday()));
		
		return this.executeHQLCount(sql.toString(), list.toArray(), true);
	}

	/**
	 * 获取所有  已配配 单据数量
	 * @return
	 * @throws ParseException 
	 */
	public int selectIpadAlreadOrders(List<Integer> custIds) {
		String clientIdSql = Common_util.parseList2SQLString(custIds);
		
		String sql = "select count(s1.order_ID) from InventoryOrder s1 where s1.order_StartTime between ? and ? and  s1.order_Status="+ InventoryOrder.STATUS_PEIHUO_COMPLETE +"  and s1.client_id in "+clientIdSql;
		// 根据条件补充部分
		List<Object> list = new ArrayList<Object>();
		list.add(Common_util.formStartDate(Common_util.getToday()));
		list.add(Common_util.formEndDate(Common_util.getToday()));
		
		return this.executeHQLCount(sql.toString(), list.toArray(), true);
	}

	/**
	 * 查询所有单据选货数量
	 * @return
	 * @throws ParseException 
	 */
	public int selectIpadNoEquidppedSnums(List<Integer> custIds)  {			
	
		DetachedCriteria criteria = DetachedCriteria.forClass(InventoryOrder.class);
		criteria.add(Restrictions.or(Restrictions.eq("order_Status", InventoryOrder.STATUS_PDA_COMPLETE), Restrictions.eq("order_Status", InventoryOrder.STATUS_PEIHUO_INPROCESS)));
		
		criteria.add(Restrictions.in("client_id",custIds));

		criteria.add(Restrictions.between("order_StartTime", Common_util.formStartDate(Common_util.getLastWeekDate()), Common_util.formEndDate(Common_util.getToday())));
		
		criteria.setProjection(Projections.sum("totalQuantity"));
		
		List<Object> result = getByCriteriaProjection(criteria, true);

        return Common_util.getProjectionSingleValue(result);
	}

	/**
	 * 查询pda配货单据(包括未配，已配，配货中)
	 * @return
	 * @throws ParseException 
	 */
	public List<InventoryOrder> searchPDAOrders(Date searchStartDate, Date searchEndDate, int clientId, int custRange) throws ParseException {
		List<InventoryOrder> orderList= new ArrayList<InventoryOrder>();		
		DetachedCriteria criteria = DetachedCriteria.forClass(InventoryOrder.class);
		
		Set<Integer> statusSet = new HashSet<Integer>();
		statusSet.add(InventoryOrder.STATUS_PEIHUO_COMPLETE);
		statusSet.add(InventoryOrder.STATUS_PDA_COMPLETE);
		statusSet.add(InventoryOrder.STATUS_PEIHUO_INPROCESS);
		criteria.add(Restrictions.in("order_Status", statusSet));
		criteria.add(Restrictions.between("order_StartTime", Common_util.formStartDate(searchStartDate), Common_util.formEndDate(searchEndDate) ));
		
		List<Integer> custIds = getClientIdByRange(custRange);
		
		criteria.add(Restrictions.in("client_id", custIds));
		
		if (clientId != Common_util.ALL_RECORD_NEW) {
			criteria.add(Restrictions.eq("client_id", clientId));
		} 
		
		criteria.addOrder(Order.desc("order_StartTime"));
		orderList = getByCritera(criteria, true);
		return orderList;
	}

	
	/**
	 * 根据order_EndTime 时间范围查询单据
	 * @param startDate
	 * @param endDate
	 * @return
	 */
	public List<InventoryOrder> selectOrdersByEndTime(Date startDate, Date endDate,List<Integer> custByTagIds) {
		DetachedCriteria criteria = DetachedCriteria.forClass(InventoryOrder.class, "order");
		criteria.add(Restrictions.eq("order.order_Status", InventoryOrder.STATUS_ACCOUNT_COMPLETE));					 				
		startDate = Common_util.formStartDate(startDate);
		endDate = Common_util.formEndDate(endDate);
		criteria.add(Restrictions.between("order.order_EndTime", startDate, endDate));
		if (custByTagIds.size() !=0) {
			criteria.add(Restrictions.in("order.client_id", custByTagIds));
		}
		List<InventoryOrder> inventoryOrders = this.getByCritera(criteria, true);
		return inventoryOrders;
	}

	
	/**
	 * 往来分析报表查询-期中销售信息赋值
	 * @param cIds
	 * @param formBean
	 * @param result
	 * @return
	 */
	public List<HeadQDealingsReportVO> setSaleInfo(List<Integer> cIds, HeadQReportFormBean formBean,
			List<HeadQDealingsReportVO> result) {
		Map<Integer, HeadQDealingsReportVO> map = new HashMap<Integer, HeadQDealingsReportVO>();
		if (result.size() != 0) {
			for (HeadQDealingsReportVO vo : result) {
				map.put(vo.getClientId(), vo);
			}
		}
		
		DetachedCriteria criteria = DetachedCriteria.forClass(InventoryOrder.class, "order");
		criteria.add(Restrictions.eq("order.order_Status", InventoryOrder.STATUS_ACCOUNT_COMPLETE));
				 				
		criteria.add(Restrictions.between("order.order_EndTime",  Common_util.formStartDate(formBean.getStartDate()),  Common_util.formStartDate(formBean.getEndDate())));
		criteria.add(Restrictions.in("client_id", cIds));
		List<InventoryOrder> inventoryOrders = this.getByCritera(criteria, true);
		if (inventoryOrders.size() !=0) {
			for (InventoryOrder inventoryOrder : inventoryOrders) {
				if (inventoryOrder.getOrder_type() == InventoryOrder.TYPE_SALES_ORDER_W){
					if (map.get(inventoryOrder.getClient_id()) != null) {
						HeadQDealingsReportVO vo = map.get(inventoryOrder.getClient_id());
						vo.setMidtermSale(vo.getMidtermSale() + inventoryOrder.getTotalWholePrice()-inventoryOrder.getTotalDiscount());
					}					
				}else if (inventoryOrder.getOrder_type() == InventoryOrder.TYPE_SALES_RETURN_ORDER_W) {
					if (map.get(inventoryOrder.getClient_id()) != null) {
						HeadQDealingsReportVO vo = map.get(inventoryOrder.getClient_id());
						vo.setMidtermReturn(vo.getMidtermSale() + inventoryOrder.getTotalWholePrice()-inventoryOrder.getTotalDiscount());
					}	
				}
			}
		}
		return result;
	}

	/**
	 * 获取时间范围内的客户ID集合信息
	 * @param startDate
	 * @param endDate
	 * @return
	 */
	public List<Integer> selectCIdsByBetweenTime(Date startDate, Date endDate) {	
		Set<Integer> set = new HashSet<Integer>();
		DetachedCriteria criteria = DetachedCriteria.forClass(InventoryOrder.class, "order");
		criteria.add(Restrictions.eq("order.order_Status", InventoryOrder.STATUS_ACCOUNT_COMPLETE));

		startDate = Common_util.formStartDate(startDate);
		endDate = Common_util.formEndDate(endDate);
		criteria.add(Restrictions.between("order.order_EndTime", Common_util.formStartDate(startDate), Common_util.formEndDate(endDate)));
		List<InventoryOrder> purchaseOrders = this.getByCritera(criteria, true);
		if (purchaseOrders.size() !=0) {
			for (InventoryOrder inventoryOrder : purchaseOrders) {
				set.add(inventoryOrder.getClient_id());
			}
		}
		List<Integer> list = new ArrayList<Integer>(set);
		return list;
	}
	

	

}
